﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace QuanLyPhongKham
{
    public partial class frmTimhoadon : BaseUserControl
    {
        private doiso.Doisototext dso = new doiso.Doisototext();
        private DAL.AccessData m;
        private string user, sql = "", s_form = "", s_mabs = "", s_tenbs = "", s_mabn = "";
        private long l_maql = 0, l_id = 0;
        private DataSet ds = new DataSet();
        private DataSet dsin = new DataSet();
        private bool bSkip = false;
        public frmTimhoadon(DAL.AccessData acc)
        {
            InitializeComponent();
            this.m = acc;
        }

        private void frmTimhoadon_Load(object sender, EventArgs e)
        {
            dsin = m.get_data("select '' as mabn,'' as hoten,'' as namsinh,'' as diachi,'' as khoaphong,'' as tenbs,'' as lydo,0.0 as sotien,'' as bangchu");
            user = m.user;
            cboTT.DisplayMember = "TENTT";
            cboTT.ValueMember = "MATT";
            cboTT.DataSource = m.get_data("select * from " + user + ".btdtt order by matt").Tables[0];
            cboTT.SelectedIndex = -1;

            cboQuan.DisplayMember = "TENQUAN";
            cboQuan.ValueMember = "MAQU";
            load_quan();

            cboPxa.DisplayMember = "TENPXA";
            cboPxa.ValueMember = "MAPHUONGXA";
            load_pxa();
        }
        private void load_quan()
        {
            if (cboTT.SelectedIndex != -1)
                cboQuan.DataSource = m.get_data("select * from " + user + ".btdquan where matt='" + cboTT.SelectedValue.ToString() + "'" + " order by maqu").Tables[0];
        }

        private void load_pxa()
        {
            if (cboQuan.SelectedIndex != -1)
            cboPxa.DataSource = m.get_data("select * from " + user + ".btdpxa where maqu='" + cboQuan.SelectedValue.ToString() + "'" + " order by maphuongxa").Tables[0];
        }

        private void butTim_Click(object sender, EventArgs e)
        {
            sql = "select a.mabn,c.hoten,c.namsinh,a.sobienlai,sum(b.soluong*b.dongia) as sotien,to_char(a.ngay,'dd/mm/yyyy') as ngaythu,g.hoten as tenbs,h.tenkp,c.diachi,f.tenpxa,e.tenquan,d.tentt,a.ghichu,a.mabs,a.maql,a.id from " + user + ".v_vienphill a, " + user + ".v_vienphict b, " + user + ".btdbn c," + user + ".btdtt d," + user + ".btdquan e," + user + ".btdpxa f," + user + ".dmbs g," + user + ".btdkp h";
            sql += " where a.id=b.id and a.mabn=c.mabn and a.mabs=g.ma and a.makp=h.makp and c.matt=d.matt and c.maqu=e.maqu and c.maphuongxa=f.maphuongxa";
            if (mabn.Text != "") sql += " and a.mabn='" + mabn.Text + "'";
            if (hoten.Text != "") sql += " and c.hotenkdau='" + m.Hoten_khongdau(hoten.Text).ToUpper() + "'";
            if (namsinh.Text != "") sql += " and c.namsinh='"+namsinh.Text+"'";
            if (cboGioitinh.SelectedIndex != -1) sql += " and c.phai=" + cboGioitinh.SelectedIndex;
            if (cboTT.SelectedIndex != -1) sql += " and c.matt='" + cboTT.SelectedValue.ToString() + "'";
            if (cboQuan.SelectedIndex != -1 && cboTT.SelectedIndex != -1) sql += " and c.maqu='" + cboQuan.SelectedValue.ToString() + "'";
            if (cboPxa.SelectedIndex != -1 && cboTT.SelectedIndex != -1) sql += " and c.maphuongxa='" + cboPxa.SelectedValue.ToString() + "'";
            sql += " group by a.mabn,c.hoten,c.namsinh,a.sobienlai,g.hoten,h.tenkp,a.ngay,c.diachi,f.tenpxa,e.tenquan,d.tentt,a.ghichu,a.mabs,a.maql,a.id";
            sql += " order by a.sobienlai desc";
            ds = m.get_data(sql);
            dataGrid1.DataSource = ds.Tables[0];
            if (!bSkip)
            {
                AddGridTableStyle();
                bSkip = true;
            }
        }
        private void AddGridTableStyle()
        {
            DataGridColoredTextBoxColumn TextCol;
            delegateGetColorRowCol de = new delegateGetColorRowCol(MyGetColorRowCol);
            DataGridTableStyle ts = new DataGridTableStyle();
            ts.MappingName = ds.Tables[0].TableName;
            ts.AlternatingBackColor = Color.Linen;
            ts.BackColor = Color.GhostWhite;
            ts.ForeColor = Color.MidnightBlue;
            ts.GridLineColor = SystemColors.Control;
            ts.HeaderBackColor = SystemColors.Control;
            ts.HeaderForeColor = Color.Navy;
            ts.SelectionBackColor = Color.Teal;
            ts.SelectionForeColor = Color.White;
            ts.ReadOnly = false;
            ts.RowHeaderWidth = 5;

            TextCol = new DataGridColoredTextBoxColumn(de, 0);
            TextCol.MappingName = "mabn";
            TextCol.HeaderText = "Mã BN";
            TextCol.Width = 70;
            TextCol.ReadOnly = true;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 1);
            TextCol.MappingName = "hoten";
            TextCol.HeaderText = "Họ và tên";
            TextCol.Width = 200;
            TextCol.ReadOnly = true;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 2);
            TextCol.MappingName = "namsinh";
            TextCol.HeaderText = "Năm sinh";
            TextCol.Width = 60;
            TextCol.ReadOnly = true;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 3);
            TextCol.MappingName = "sobienlai";
            TextCol.HeaderText = "Số hoá đơn";
            TextCol.Width = 80;
            TextCol.ReadOnly = true;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 4);
            TextCol.MappingName = "sotien";
            TextCol.HeaderText = "Số tiền";
            TextCol.Width = 160;
            TextCol.Format = "###,###,##0";
            TextCol.ReadOnly = true;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 5);
            TextCol.MappingName = "ngaythu";
            TextCol.HeaderText = "Ngày thu";
            TextCol.Width = 100;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 6);
            TextCol.MappingName = "tenbs";
            TextCol.HeaderText = "Bác sĩ khám";
            TextCol.Width = 150;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 7);
            TextCol.MappingName = "tenkp";
            TextCol.HeaderText = "Phòng khám";
            TextCol.Width = 100;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 8);
            TextCol.MappingName = "diachi";
            TextCol.HeaderText = "Địa chỉ";
            TextCol.Width = 160;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 9);
            TextCol.MappingName = "tenpxa";
            TextCol.HeaderText = "Phường/Xã";
            TextCol.Width = 100;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 10);
            TextCol.MappingName = "tenquan";
            TextCol.HeaderText = "Quận/Huyện";
            TextCol.Width = 100;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 11);
            TextCol.MappingName = "tentt";
            TextCol.HeaderText = "Tỉnh/Thành";
            TextCol.Width = 100;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 12);
            TextCol.MappingName = "ghichu";
            TextCol.HeaderText = "";
            TextCol.Width = 0;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 13);
            TextCol.MappingName = "mabs";
            TextCol.HeaderText = "";
            TextCol.Width = 0;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 14);
            TextCol.MappingName = "maql";
            TextCol.HeaderText = "";
            TextCol.Width = 0;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);

            TextCol = new DataGridColoredTextBoxColumn(de, 15);
            TextCol.MappingName = "id";
            TextCol.HeaderText = "";
            TextCol.Width = 0;
            ts.GridColumnStyles.Add(TextCol);
            dataGrid1.TableStyles.Add(ts);
        }
        public Color MyGetColorRowCol(int row, int col)
        {
            Color c = Color.Navy;
            try
            {
                if (row == dataGrid1.CurrentRowIndex)
                {
                    dataGrid1.Select(row);
                    c = Color.White;
                }
            }
            catch
            {
            }
            return c;
        }
        public delegate Color delegateGetColorRowCol(int row, int col);
        public class DataGridColoredTextBoxColumn : DataGridTextBoxColumn
        {
            private delegateGetColorRowCol _getColorRowCol;
            private int _column;
            public DataGridColoredTextBoxColumn(delegateGetColorRowCol getcolorRowCol, int column)
            {
                _getColorRowCol = getcolorRowCol;
                _column = column;
            }
            protected override void Paint(System.Drawing.Graphics g, System.Drawing.Rectangle bounds, System.Windows.Forms.CurrencyManager source, int rowNum, System.Drawing.Brush backBrush, System.Drawing.Brush foreBrush, bool alignToRight)
            {
                try
                {
                    foreBrush = new SolidBrush(_getColorRowCol(rowNum, this._column));
                }
                catch { }
                finally
                {
                    base.Paint(g, bounds, source, rowNum, backBrush, foreBrush, alignToRight);
                }
            }
        }
        private void dataGrid1_CurrentCellChanged(object sender, EventArgs e)
        {
            s_form = ""; l_maql = 0; l_id = 0;
            try 
            {
                s_mabn = dataGrid1[dataGrid1.CurrentCell.RowNumber, 0].ToString();
                s_form = dataGrid1[dataGrid1.CurrentCell.RowNumber, 12].ToString();
                s_mabs = dataGrid1[dataGrid1.CurrentCell.RowNumber, 13].ToString();
                s_tenbs = dataGrid1[dataGrid1.CurrentCell.RowNumber, 6].ToString();
                l_maql = long.Parse(dataGrid1[dataGrid1.CurrentCell.RowNumber, 14].ToString());
                l_id = long.Parse(dataGrid1[dataGrid1.CurrentCell.RowNumber, 15].ToString());
            }
            catch { s_form = ""; s_mabs = ""; s_tenbs = ""; s_mabn = ""; l_maql = 0; l_id = 0; }
        }
        private void load_report()
        {
            DataSet dst = new DataSet();
            string[] var_form = s_form.Split(',');
            string s_report = "";
            for (int ii = 0; ii < var_form.Length; ii++)
            {
                sql = "";
                switch (var_form[ii].ToString())
                {
                    case "pKhamvu":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_khamvu a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_khamvu.rpt";
                        break;
                    case "pKhampk":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_khampk a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_khampk.rpt";
                        break;
                    case "pKhamthai":
                        sql = "select to_char(a.dukiensinh,'dd/mm/yyyy') as dukiensinh,a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_khamthai a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_khamthai.rpt";
                        break;
                    case "pHiemmuon":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_hiemmuon a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_hiemmuon.rpt";
                        break;
                    case "pSieuam":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuam4d a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuam4d.rpt";
                        break;
                    case "pSieuam_tn":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuamthai a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuamthai_nghen.rpt";
                        break;
                    case "pSieuam_th":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuamthai_3thang a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuamthai.rpt";
                        break;
                    case "pSieuam_pk":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuampk a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuampk.rpt";
                        break;
                    case "pTheodoi":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_theodoitimthai a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_theodoithai.rpt";
                        break;
                    case "pSieuam4D":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuammau a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuammau.rpt";
                        break;
                    case "pSieuam_12":
                        sql = "select a.*,b.hoten,b.namsinh,b.phai,b.cholam,trim(b.diachi||' '||e.tenpxa||' '||d.tenquan||' '||c.tentt) as diachi from " + user + ".ba_sieuam4d_12tuan a, " + user + ".btdbn b, " + user + ".btdtt c, " + user + ".btdquan d, " + user + ".btdpxa e ";
                        sql += " where a.mabn=b.mabn and b.matt=c.matt and b.maqu=d.maqu and b.maphuongxa=e.maphuongxa and a.mabn='" + s_mabn + "' and a.maql=" + l_maql;
                        dst = m.get_data(sql);
                        s_report = "ba_sieuam4d_12tuan.rpt";
                        break;
                }
                if (sql != "")
                {
                    if (dst.Tables[0].Rows.Count == 0) continue;
                    DataColumn dc = new DataColumn("Image", typeof(byte[]));
                    dst.Tables[0].Columns.Add(dc);
                    if (File.Exists("..\\..\\..\\chuky\\" + s_mabs + ".bmp"))
                    {
                        FileStream fstr = new FileStream("..\\..\\..\\chuky\\" + s_mabs + ".bmp", FileMode.Open, FileAccess.Read);
                        byte[] image = new byte[fstr.Length];
                        fstr.Read(image, 0, System.Convert.ToInt32(fstr.Length));
                        fstr.Close();
                        foreach (DataRow r1 in dst.Tables[0].Rows) r1["Image"] = image;
                    }
                    frmReport f = new frmReport(m, dst.Tables[0], "", s_report, "", "", "", "Bs. " + s_tenbs, m.ngayhienhanh_server, "", "", "", "", "", "", "");
                    f.Title = "Loại";
                    this.FormMain.OpenTab(f);
                }
            }
        }

        private void mnuKetqua_Click(object sender, EventArgs e)
        {
            if (l_maql == 0) return;
            if (s_form == "")
            {
                MessageBox.Show("Bệnh nhân không có phiếu kết quả!", DAL.AccessData.Msg);
                return;
            }
            load_report();
        }

        private void mabn_Validated(object sender, EventArgs e)
        {
            if (mabn.Text.Trim() != "" && mabn.Text.Length >= 3)
            {
                mabn.Text = mabn.Text.Substring(0, 2) + mabn.Text.Substring(2).PadLeft(6, '0');
                butTim_Click(null, null);
            }
        }

        private void cboTT_SelectedIndexChanged(object sender, EventArgs e)
        {
            try { load_quan(); }
            catch { }
            cboQuan.SelectedIndex = -1;
        }

        private void cboQuan_SelectedIndexChanged(object sender, EventArgs e)
        {
            try { load_pxa(); }
            catch { }
            cboPxa.SelectedIndex = -1;
        }

        private void butMoi_Click(object sender, EventArgs e)
        {
            mabn.Text = "";
            hoten.Text = "";
            namsinh.Text = ""; cboGioitinh.SelectedIndex = -1;
            cboTT.SelectedIndex = -1;
            cboQuan.SelectedIndex = -1;
            cboPxa.SelectedIndex = -1;
            dienthoai.Text = "";
            coquan.Text = "";
            didong.Text = "";
            ds.Clear();
            mabn.Focus();
        }

        private void mabn_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyCode == Keys.Enter)
            {
                SendKeys.Send("{Tab}");
            }
        }

        private void mnuInhoadon_Click(object sender, EventArgs e)
        {
            if (l_id == 0) return;
            dsin.Clear();
            foreach (DataRow r in ds.Tables[0].Select("id=" + l_id))
            {
                DataRow rr = dsin.Tables[0].NewRow();
                rr["mabn"] = r["mabn"].ToString();
                rr["hoten"] = r["hoten"].ToString();
                rr["namsinh"] = r["namsinh"].ToString();
                rr["diachi"] = r["diachi"].ToString() + " " + r["tenpxa"].ToString() + "-" + r["tenquan"].ToString() + "-" + r["tentt"].ToString();
                rr["khoaphong"] = r["tenkp"].ToString();
                rr["tenbs"] = r["tenbs"].ToString();
                rr["lydo"] = "Thu tiền khám bệnh";
                rr["sotien"] = r["sotien"].ToString();
                rr["bangchu"] = dso.Doiso_Unicode(r["sotien"].ToString());
                dsin.Tables[0].Rows.Add(rr);
                break;
            }
            dsin.AcceptChanges();
            dsin.WriteXml(@"..\v_bienlai.xml", XmlWriteMode.WriteSchema);
            frmReport f = new frmReport(m, dsin.Tables[0], "", "v_bienlai.rpt", "", "", "", "", m.ngayhienhanh_server, "", "", "", "", "", "", "");
            f.Title = "Biên lai";
            this.FormMain.OpenTab(f);
        }
    }
}